package com.demo;

import com.demo.beans.Student;
import com.demo.beans.User;
import com.demo.utils.DruidUtil;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Base64;
import java.util.List;

/**
 * 使用 druid 数据库连接池技术
 * 使用DBUtils 数据库操作工具类
 */
public class Demo {
    public static void main(String[] args) {

    }

    @Test
    public void insertData() throws SQLException {
        //创建sql执行工具
        QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
        Connection conn = DruidUtil.getConn();
        String sql = "insert into user(user,psd) values(?,?)";
        int rows = qr.update(conn,sql,"zhangsan","147");
        System.out.println("执行影响的行数"+rows);
        DbUtils.closeQuietly(conn);
    }

    @Test
    public void queryUser(){
        try {
            QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
            Connection conn = DruidUtil.getConn();
            String sql = "select user,psd from user";
            List<User> result = qr.query(conn, sql, new BeanListHandler<User>(User.class));
            for(User user: result){
                System.out.println("--->"+user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Test
    public void queryUser2(){
        try {

            QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
            String sql = "select user,psd from user";
            // 设置数据源后，此处可以不传 connection 参数，会自动从数据源中获取
            List<User> result = qr.query(sql, new BeanListHandler<User>(User.class));
            for(User user: result){
                System.out.println("--->"+user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 当javaBean中属性名和数据库中列名 不一致时
     */
    @Test
    public void queryUser3(){
        try {
            QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
            String sql = "select id,_name as 'name',_age as 'age' from t_stu";
            // 设置数据源后，此处可以不传 connection 参数，会自动从数据源中获取
            List<Student> result = qr.query(sql, new BeanListHandler<Student>(Student.class));
            for(Student item: result){
                System.out.println("--->"+item);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Test
    public void queryOne(){
        try {
            QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
            Connection conn = DruidUtil.getConn();
            String sql = "select user,psd from user where user=?";
            User result = qr.query(conn, sql, new BeanHandler<User>(User.class), "a");
            System.out.println("--->"+result);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
